package com.seniorproject.aims;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;

public class QueryStudent {
	
	public static Connection connectDB() {
		String hostProps = "localhost";
		String usernameProps  = "vXs6416S/z3VN}K";
		String passwordProps  = "CmwKTAQUFfqazKNz";
		String databaseProps = "AIMS";
		
		Connection connect = null;
		
		try {
			Class.forName("com.mysql.jdbc.Driver");
			
			connect =  DriverManager.getConnection("jdbc:mysql://" + hostProps  + "/" + databaseProps +
					"?user=" + usernameProps  + "&password=" + passwordProps+ "&characterEncoding=tis620");
			
			if(connect != null){
				System.out.println("Database Connect Sucesses.");
			} else {
				System.out.println("Database Connect Failed.");	
			}


		} catch (Exception e) { 
			System.out.println(e);  
		}
		
		return connect;
	}

	
	
//////////////////////////////////////COUNT STUDENT FOLLOW DEPARTMENT & YEAR//////////////////////////////////////////////////////////////
	
	
	// count student follow department and year 
	public static int countStudentDepartment(int departmentId, int startDate, boolean yearHigh) {
        System.out.println("@ count student department year");///try///
        System.out.println("departmentId : "+departmentId);///try///
        System.out.println("startDate : "+startDate);///try///
        System.out.println("yearHigh : "+yearHigh);///try///
        
        
        int count = 0;
        Connection connect = connectDB();
        
		try {
			String sql = "SELECT count(student_id) AS count "
					+ " FROM student "
					+ " WHERE department_id=" + departmentId;
			
			if(yearHigh) {
				sql = sql +" AND start_date < '" + startDate + "' ";
			}
			else {
				sql = sql +" AND start_date LIKE '%" + startDate + "%' ";
			}
			
			ResultSet rs = connect.createStatement().executeQuery(sql);
			
			if(rs.next()) {
				count = rs.getInt("count");
				
				
			}
			
			rs.close();
			connect.close();
		} catch (Exception e) { 
			System.out.println(e);  
		}
		
		return count;
	}
		
	// count student follow department, year and status
	public static int countStudentStatus(int departmentId, int startDate, int statusId , boolean yearHigh) {
        System.out.println("@ count student department year status");///try///
        System.out.println("departmentId : "+departmentId);///try///
        System.out.println("startDate : "+startDate);///try///
        System.out.println("yearHigh : "+yearHigh);///try///
        System.out.println("statusId : "+statusId);///try///
        
        
        int count = 0;
        Connection connect = connectDB();
        
		try {
			String sql = "SELECT count(student_id) AS count "
					+ " FROM student "
					+ " WHERE department_id=" + departmentId + " AND status_id=" + statusId;
			
			if(yearHigh) {
				sql = sql +" AND start_date < '" + startDate + "' ";
			}
			else {
				sql = sql +" AND start_date LIKE '%" + startDate + "%' ";
			}
			
			ResultSet rs = connect.createStatement().executeQuery(sql);
			
			if(rs.next()) {
				count = rs.getInt("count");
				
				
			}
			
			rs.close();
			connect.close();
		} catch (Exception e) { 
			System.out.println(e);  
		}
		
		return count;
	}
	
	// count student follow department, year and gender
	public static int countStudentGender(int departmentId, int startDate, String gender, boolean yearHigh) {
        System.out.println("@ count student department year gender");///try///
        System.out.println("departmentId : "+departmentId);///try///
        System.out.println("startDate : "+startDate);///try///
        System.out.println("yearHigh : "+yearHigh);///try///
        System.out.println("gender : "+gender);///try///
        
        int count = 0;
        Connection connect = connectDB();
        
		try {
			String sql = "SELECT count(student_id) AS count "
					+ " FROM student S, people P "
					+ " WHERE P.index=S.index "
					+ " AND S.department_id="+departmentId+" AND P.gender='"+gender+"'";
			
			if(yearHigh) {
				sql = sql +" AND S.start_date < '" + startDate + "' ";
			}
			else {
				sql = sql +" AND S.start_date LIKE '%" + startDate + "%' ";
			}
			
			ResultSet rs = connect.createStatement().executeQuery(sql);
			
			if(rs.next()) {
				count = rs.getInt("count");
				
				
			}
			
			rs.close();
			connect.close();
		} catch (Exception e) { 
			System.out.println(e);  
		}
		
		return count;
	}
	
	// count student follow transfer in department, year
	public static int countStudentTransferIn(int departmentId, int startDate, boolean yearHigh) {
        System.out.println("@ count student transfer in");///try///
        System.out.println("departmentId : "+departmentId);///try///
        System.out.println("startDate : "+startDate);///try///
        System.out.println("yearHigh : "+yearHigh);///try///
        
        int count = 0;
        Connection connect = connectDB();
        
		try {
			String sql = "SELECT count(student_id) AS count "
					+ " FROM student "
					+ " WHERE department_id="+departmentId
					+ " AND transfer=1";
			
			if(yearHigh) {
				sql = sql +" AND start_date < '" + startDate + "' ";
			}
			else {
				sql = sql +" AND start_date LIKE '%" + startDate + "%' ";
			}
			
			ResultSet rs = connect.createStatement().executeQuery(sql);
			
			if(rs.next()) {
				count = rs.getInt("count");
				
				
			}
			
			rs.close();
			connect.close();
		} catch (Exception e) { 
			System.out.println(e);  
		}
		
		return count;
	}

	// count student follow transfer out department, year
	public static int countStudentTransferOut(int departmentId, int startDate, boolean yearHigh) {
        System.out.println("@ count student transfer out");///try///
        System.out.println("departmentId : "+departmentId);///try///
        System.out.println("startDate : "+startDate);///try///
        System.out.println("yearHigh : "+yearHigh);///try///
        
        int count = 0;
        Connection connect = connectDB();
        
		try {
			String sql = "SELECT count(student_id) AS count "
					+ " FROM student "
					+ " WHERE department_id_old="+departmentId
					+ " AND transfer=1";
			
			if(yearHigh) {
				sql = sql +" AND start_date < '" + startDate + "' ";
			}
			else {
				sql = sql +" AND start_date LIKE '%" + startDate + "%' ";
			}
			
			ResultSet rs = connect.createStatement().executeQuery(sql);
			
			if(rs.next()) {
				count = rs.getInt("count");
				
				
			}
			
			rs.close();
			connect.close();
		} catch (Exception e) { 
			System.out.println(e);  
		}
		
		return count;
	}
	
	// count student follow department, year and entrance
	public static int countStudentEntrance(int departmentId, int startDate, int entranceId , boolean yearHigh) {
        System.out.println("@ count student department year entrance");///try///
        System.out.println("departmentId : "+departmentId);///try///
        System.out.println("startDate : "+startDate);///try///
        System.out.println("yearHigh : "+yearHigh);///try///
        System.out.println("entranceId : "+entranceId);///try///
        
        int count = 0;
        Connection connect = connectDB();
        
		try {
			String sql = "SELECT count(student_id) AS count "
					+ " FROM student "
					+ " WHERE department_id=" + departmentId + " AND entrance_id=" + entranceId;
			
			if(yearHigh) {
				sql = sql +" AND start_date < '" + startDate + "' ";
			}
			else {
				sql = sql +" AND start_date LIKE '%" + startDate + "%' ";
			}
			
			ResultSet rs = connect.createStatement().executeQuery(sql);
			
			if(rs.next()) {
				count = rs.getInt("count");
				
				
			}
			
			rs.close();
			connect.close();
		} catch (Exception e) { 
			System.out.println(e);  
		}
		
		return count;
	}
	
	
	
	
//////////////////////////////////////COUNT STUDENT DEPARTMENT EE - EC//////////////////////////////////////////////////////////////
	
	// count student EE-EC follow department and year 
	public static int countStudentECDepartment(int departmentId, int startDate, boolean yearHigh,String curriculumName) {
        System.out.println("@ count student department year");///try///
        System.out.println("departmentId : "+departmentId);///try///
        System.out.println("startDate : "+startDate);///try///
        System.out.println("yearHigh : "+yearHigh);///try///
        System.out.println("curriculum : "+curriculumName);///try///
        
        
        int count = 0;
        Connection connect = connectDB();
        
		try {
			String sql = "SELECT count(S.student_id) AS count "
					+ " FROM student S, curriculum C"
					+ " WHERE S.curriculum_id=C.curriculum_id AND S.department_id=" + departmentId
					+ " AND C.curriculum_name_th LIKE '%(ไฟฟ้าสื่อสาร)'";
			
			if(yearHigh) {
				sql = sql +" AND start_date < '" + startDate + "' ";
			}
			else {
				sql = sql +" AND start_date LIKE '%" + startDate + "%' ";
			}
			
			ResultSet rs = connect.createStatement().executeQuery(sql);
			
			if(rs.next()) {
				count = rs.getInt("count");
				
				
			}
			
			rs.close();
			connect.close();
		} catch (Exception e) { 
			System.out.println(e);  
		}
		
		return count;
	}
		
	// count student EE-EC follow department, year and status
	public static int countStudentECStatus(int departmentId, int startDate, int statusId , boolean yearHigh, String curriculumName) {
        System.out.println("@ count student department year status");///try///
        System.out.println("departmentId : "+departmentId);///try///
        System.out.println("startDate : "+startDate);///try///
        System.out.println("yearHigh : "+yearHigh);///try///
        System.out.println("statusId : "+statusId);///try///
        System.out.println("curriculum : "+curriculumName);///try///
        
        
        int count = 0;
        Connection connect = connectDB();
        
		try {
			String sql = "SELECT count(S.student_id) AS count "
					+ " FROM student S, curriculum C"
					+ " WHERE S.curriculum_id=C.curriculum_id AND S.department_id=" + departmentId 
					+ " AND S.status_id=" + statusId + " AND C.curriculum_name_th LIKE '%(ไฟฟ้าสื่อสาร)'";
			
			if(yearHigh) {
				sql = sql +" AND start_date < '" + startDate + "' ";
			}
			else {
				sql = sql +" AND start_date LIKE '%" + startDate + "%' ";
			}
			
			ResultSet rs = connect.createStatement().executeQuery(sql);
			
			if(rs.next()) {
				count = rs.getInt("count");
				
				
			}
			
			rs.close();
			connect.close();
		} catch (Exception e) { 
			System.out.println(e);  
		}
		
		return count;
	}
	
	// count student EE-EC follow department, year and gender
	public static int countStudentECGender(int departmentId, int startDate, String gender, boolean yearHigh, String curriculumName) {
        System.out.println("@ count student department year gender");///try///
        System.out.println("departmentId : "+departmentId);///try///
        System.out.println("startDate : "+startDate);///try///
        System.out.println("yearHigh : "+yearHigh);///try///
        System.out.println("gender : "+gender);///try///
        System.out.println("curriculum : "+curriculumName);///try///
        
        int count = 0;
        Connection connect = connectDB();
        
		try {
			String sql = "SELECT count(student_id) AS count "
					+ " FROM student S, people P, curriculum C "
					+ " WHERE P.index=S.index AND S.curriculum_id=C.curriculum_id "
					+ " AND S.department_id="+departmentId+" AND P.gender='"+gender+"'"
					+ " AND C.curriculum_name_th LIKE '%(ไฟฟ้าสื่อสาร)'";
			
			if(yearHigh) {
				sql = sql +" AND S.start_date < '" + startDate + "' ";
			}
			else {
				sql = sql +" AND S.start_date LIKE '%" + startDate + "%' ";
			}
			
			ResultSet rs = connect.createStatement().executeQuery(sql);
			
			if(rs.next()) {
				count = rs.getInt("count");
				
			}
			
			rs.close();
			connect.close();
		} catch (Exception e) { 
			System.out.println(e);  
		}
		
		return count;
	}
	
	// count student EE-EC follow transfer in department, year
	public static int countStudentECTransferIn(int departmentId, int startDate, boolean yearHigh, String curriculumName) {
        System.out.println("@ count student transfer in");///try///
        System.out.println("departmentId : "+departmentId);///try///
        System.out.println("startDate : "+startDate);///try///
        System.out.println("yearHigh : "+yearHigh);///try///
        System.out.println("curriculum : "+curriculumName);///try///
        
        int count = 0;
        Connection connect = connectDB();
        
		try {
			String sql = "SELECT count(student_id) AS count "
					+ " FROM student S, curriculum C"
					+ " WHERE S.curriculum_id=C.curriculum_id AND S.department_id="+departmentId
					+ " AND S.transfer=1" + " AND C.curriculum_name_th LIKE '%(ไฟฟ้าสื่อสาร)'";
			
			if(yearHigh) {
				sql = sql +" AND start_date < '" + startDate + "' ";
			}
			else {
				sql = sql +" AND start_date LIKE '%" + startDate + "%' ";
			}
			
			ResultSet rs = connect.createStatement().executeQuery(sql);
			
			if(rs.next()) {
				count = rs.getInt("count");
				
				
			}
			
			rs.close();
			connect.close();
		} catch (Exception e) { 
			System.out.println(e);  
		}
		
		return count;
	}

	// count student EE-EC follow transfer out department, year
	public static int countStudentECTransferOut(int departmentId, int startDate, boolean yearHigh, String curriculumName) {
        System.out.println("@ count student transfer out");///try///
        System.out.println("departmentId : "+departmentId);///try///
        System.out.println("startDate : "+startDate);///try///
        System.out.println("yearHigh : "+yearHigh);///try///
        System.out.println("curriculum : "+curriculumName);///try///
        
        int count = 0;
        Connection connect = connectDB();
        
		try {
			String sql = "SELECT count(student_id) AS count "
					+ " FROM student S, curriculum_id C "
					+ " WHERE S.curriculum_id=C.curriculum_id AND S.department_id_old="+departmentId
					+ " AND S.transfer=1" + " AND C.curriculum_name_th LIKE '%(ไฟฟ้าสื่อสาร)'";
			
			if(yearHigh) {
				sql = sql +" AND start_date < '" + startDate + "' ";
			}
			else {
				sql = sql +" AND start_date LIKE '%" + startDate + "%' ";
			}
			
			ResultSet rs = connect.createStatement().executeQuery(sql);
			
			if(rs.next()) {
				count = rs.getInt("count");
				
				
			}
			
			rs.close();
			connect.close();
		} catch (Exception e) { 
			System.out.println(e);  
		}
		
		return count;
	}
	
	// count student EE-EC follow department, year and entrance
	public static int countStudentECEntrance(int departmentId, int startDate, int entranceId , boolean yearHigh, String curriculumName) {
        System.out.println("@ count student department year entrance");///try///
        System.out.println("departmentId : "+departmentId);///try///
        System.out.println("startDate : "+startDate);///try///
        System.out.println("yearHigh : "+yearHigh);///try///
        System.out.println("entranceId : "+entranceId);///try///
        System.out.println("curriculum : "+curriculumName);///try///
        
        int count = 0;
        Connection connect = connectDB();
        
		try {
			String sql = "SELECT count(student_id) AS count "
					+ " FROM student S, curriculum_id C "
					+ " WHERE S.curriculum_id=C.curriculum_id AND department_id=" + departmentId 
					+ " AND entrance_id=" + entranceId + " AND C.curriculum_name_th LIKE '%(ไฟฟ้าสื่อสาร)'";
			
			if(yearHigh) {
				sql = sql +" AND start_date < '" + startDate + "' ";
			}
			else {
				sql = sql +" AND start_date LIKE '%" + startDate + "%' ";
			}
			
			ResultSet rs = connect.createStatement().executeQuery(sql);
			
			if(rs.next()) {
				count = rs.getInt("count");
				
				
			}
			
			rs.close();
			connect.close();
		} catch (Exception e) { 
			System.out.println(e);  
		}
		
		return count;
	}
	
	
	
	
//////////////////////////////////////COUNT ALL STUDENT//////////////////////////////////////////////////////////////
	
	// count all student follow year 
	public static int countAllDepartment(int startDate, boolean yearHigh) {
        System.out.println("@ count all student year");///try///
        System.out.println("startDate : "+startDate);///try///
        System.out.println("yearHigh : "+yearHigh);///try///
        
        int count = 0;
        Connection connect = connectDB();
        
		try {
			String sql = "SELECT count(student_id) AS count "
					+ " FROM student "
					+ "WHERE ";
			
			if(yearHigh) {
				sql = sql +" start_date < '" + startDate + "' ";
			}
			else {
				sql = sql +" start_date LIKE '%" + startDate + "%' ";
			}
			
			ResultSet rs = connect.createStatement().executeQuery(sql);
			
			if(rs.next()) {
				count = rs.getInt("count");
				
				
			}
			
			rs.close();
			connect.close();
		} catch (Exception e) { 
			System.out.println(e);  
		}
		
		
		
		return count;
	}
		
	// count all student follow year and status
	public static int countAllStatus(int startDate, int statusId , boolean yearHigh) {
        System.out.println("@ count all student year status");///try///
        System.out.println("startDate : "+startDate);///try///
        System.out.println("yearHigh : "+yearHigh);///try///
        System.out.println("statusId : "+statusId);///try///
        
        int count = 0;
        Connection connect = connectDB();
        
		try {
			String sql = "SELECT count(student_id) AS count "
					+ " FROM student "
					+ " WHERE status_id=" + statusId;
			
			if(yearHigh) {
				sql = sql +" AND start_date < '" + startDate + "' ";
			}
			else {
				sql = sql +" AND start_date LIKE '%" + startDate + "%' ";
			}
			
			ResultSet rs = connect.createStatement().executeQuery(sql);
			
			if(rs.next()) {
				count = rs.getInt("count");
				
				
			}
			
			rs.close();
			connect.close();
		} catch (Exception e) { 
			System.out.println(e);  
		}
		
		return count;
	}
	
	// count all student follow year and gender
	public static int countAllGender(int startDate, String gender, boolean yearHigh) {
        System.out.println("@ count all student  year gender");///try///
        System.out.println("startDate : "+startDate);///try///
        System.out.println("yearHigh : "+yearHigh);///try///
        System.out.println("gender : "+gender);///try///
        
        int count = 0;
        Connection connect = connectDB();
        
		try {
			String sql = "SELECT count(student_id) AS count "
					+ " FROM student S, people P "
					+ " WHERE P.index=S.index "
					+ " AND gender='"+gender+"'";
			
			if(yearHigh) {
				sql = sql +" AND S.start_date < '" + startDate + "' ";
			}
			else {
				sql = sql +" AND S.start_date LIKE '%" + startDate + "%' ";
			}
			
			ResultSet rs = connect.createStatement().executeQuery(sql);
			
			if(rs.next()) {
				count = rs.getInt("count");
				
				
			}
			
			rs.close();
			connect.close();
		} catch (Exception e) { 
			System.out.println(e);  
		}
		
		return count;
	}
	
	// count all student follow transfer in department, year
	public static int countAllTransferIn(int startDate, boolean yearHigh) {
        System.out.println("@ count student transfer in");///try///
        System.out.println("startDate : "+startDate);///try///
        System.out.println("yearHigh : "+yearHigh);///try///
        
        int count = 0;
        Connection connect = connectDB();
        
		try {
			String sql = "SELECT count(student_id) AS count "
					+ " FROM student "
					+ " WHERE transfer=1";
			
			if(yearHigh) {
				sql = sql +" AND start_date < '" + startDate + "' ";
			}
			else {
				sql = sql +" AND start_date LIKE '%" + startDate + "%' ";
			}
			
			ResultSet rs = connect.createStatement().executeQuery(sql);
			
			if(rs.next()) {
				count = rs.getInt("count");
				
				
			}
			
			rs.close();
			connect.close();
		} catch (Exception e) { 
			System.out.println(e);  
		}
		
		return count;
	}

	// count all student follow transfer out department, year
	public static int countAllTransferOut(int startDate, boolean yearHigh) {
        System.out.println("@ count student transfer out");///try///
        System.out.println("startDate : "+startDate);///try///
        System.out.println("yearHigh : "+yearHigh);///try///
        
        int count = 0;
        Connection connect = connectDB();
        
		try {
			String sql = "SELECT count(student_id) AS count "
					+ " FROM student "
					+ " WHERE transfer=1";
			
			if(yearHigh) {
				sql = sql +" AND start_date < '" + startDate + "' ";
			}
			else {
				sql = sql +" AND start_date LIKE '%" + startDate + "%' ";
			}
			
			ResultSet rs = connect.createStatement().executeQuery(sql);
			
			if(rs.next()) {
				count = rs.getInt("count");
				
				
			}
			
			rs.close();
			connect.close();
		} catch (Exception e) { 
			System.out.println(e);  
		}
		
		return count;
	}
	
	// count all student follow department, year and entrance
	public static int countAllEntrance(int startDate, int entranceId , boolean yearHigh) {
        System.out.println("@ count all student year entrance");///try///
        System.out.println("startDate : "+startDate);///try///
        System.out.println("yearHigh : "+yearHigh);///try///
        System.out.println("entranceId : "+entranceId);///try///
        
        
        int count = 0;
        Connection connect = connectDB();
        
		try {
			String sql = "SELECT count(student_id) AS count "
					+ " FROM student "
					+ " WHERE entrance_id=" + entranceId;
			
			if(yearHigh) {
				sql = sql +" AND start_date < '" + startDate + "' ";
			}
			else {
				sql = sql +" AND start_date LIKE '%" + startDate + "%' ";
			}
			
			ResultSet rs = connect.createStatement().executeQuery(sql);
			
			if(rs.next()) {
				count = rs.getInt("count");
				
				
			}
			
			rs.close();
			connect.close();
		} catch (Exception e) { 
			System.out.println(e);  
		}
		
		return count;
	}
	
	

}
